-- =============================================
-- 用户行为监控数据库表
-- 用于检测用户虚假操作行为
-- 创建日期: 2024年
-- =============================================

USE [refunddb]
GO

-- =============================================
-- 1. 用户行为日志表
-- =============================================
CREATE TABLE [dbo].[lzfy_user_behavior_log](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [user_id] [varchar](50) NOT NULL,                    -- 用户ID
    [user_name] [varchar](100) NULL,                     -- 用户名
    [session_id] [varchar](100) NOT NULL,               -- 会话ID
    [task_detail_id] [bigint] NULL,                      -- 任务详情ID
    [patient_id] [varchar](50) NULL,                     -- 患者ID
    [action_type] [varchar](50) NOT NULL,               -- 操作类型
    [action_data] [nvarchar](max) NULL,                 -- 操作数据(JSON格式)
    [timestamp] [datetime2](7) NOT NULL,                -- 操作时间戳
    [page_url] [varchar](500) NULL,                     -- 页面URL
    [user_agent] [varchar](1000) NULL,                  -- 用户代理
    [ip_address] [varchar](50) NULL,                    -- IP地址
    [created_at] [datetime2](7) NOT NULL DEFAULT GETDATE(),
    CONSTRAINT [PK_lzfy_user_behavior_log] PRIMARY KEY CLUSTERED ([id] ASC)
)
GO

-- =============================================
-- 2. 用户操作会话表
-- =============================================
CREATE TABLE [dbo].[lzfy_user_operation_session](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [session_id] [varchar](100) NOT NULL,               -- 会话ID
    [user_id] [varchar](50) NOT NULL,                   -- 用户ID
    [task_detail_id] [bigint] NOT NULL,                 -- 任务详情ID
    [patient_id] [varchar](50) NOT NULL,                -- 患者ID
    [patient_name] [varchar](100) NULL,                 -- 患者姓名
    [phone_number] [varchar](50) NULL,                  -- 电话号码
    [start_time] [datetime2](7) NOT NULL,               -- 开始时间(点击详情)
    [phone_view_time] [datetime2](7) NULL,              -- 查看电话时间
    [first_input_time] [datetime2](7) NULL,             -- 首次输入时间
    [last_input_time] [datetime2](7) NULL,              -- 最后输入时间
    [save_time] [datetime2](7) NULL,                    -- 保存时间
    [total_duration] [int] NULL,                        -- 总耗时(秒)
    [phone_to_save_duration] [int] NULL,                -- 查看电话到保存的耗时(秒)
    [input_duration] [int] NULL,                        -- 输入耗时(秒)
    [input_char_count] [int] NULL,                      -- 输入字符数
    [contact_result] [varchar](100) NULL,               -- 联系结果
    [user_notes] [nvarchar](500) NULL,                  -- 用户备注
    [is_completed] [bit] NOT NULL DEFAULT 0,            -- 是否完成
    [risk_score] [decimal](5,2) NULL,                   -- 风险评分
    [risk_factors] [nvarchar](max) NULL,                -- 风险因素(JSON格式)
    [created_at] [datetime2](7) NOT NULL DEFAULT GETDATE(),
    [updated_at] [datetime2](7) NOT NULL DEFAULT GETDATE(),
    CONSTRAINT [PK_lzfy_user_operation_session] PRIMARY KEY CLUSTERED ([id] ASC)
)
GO

-- =============================================
-- 3. 用户行为分析规则表
-- =============================================
CREATE TABLE [dbo].[lzfy_behavior_analysis_rules](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [rule_name] [varchar](100) NOT NULL,                -- 规则名称
    [rule_type] [varchar](50) NOT NULL,                 -- 规则类型
    [rule_description] [nvarchar](500) NULL,            -- 规则描述
    [min_duration] [int] NULL,                          -- 最小耗时(秒)
    [max_duration] [int] NULL,                          -- 最大耗时(秒)
    [weight] [decimal](5,2) NOT NULL DEFAULT 1.0,       -- 权重
    [is_active] [bit] NOT NULL DEFAULT 1,               -- 是否启用
    [created_at] [datetime2](7) NOT NULL DEFAULT GETDATE(),
    [updated_at] [datetime2](7) NOT NULL DEFAULT GETDATE(),
    CONSTRAINT [PK_lzfy_behavior_analysis_rules] PRIMARY KEY CLUSTERED ([id] ASC)
)
GO

-- =============================================
-- 4. 用户行为统计表
-- =============================================
CREATE TABLE [dbo].[lzfy_user_behavior_statistics](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [user_id] [varchar](50) NOT NULL,                   -- 用户ID
    [stat_date] [date] NOT NULL,                        -- 统计日期
    [total_operations] [int] NOT NULL DEFAULT 0,        -- 总操作数
    [completed_operations] [int] NOT NULL DEFAULT 0,    -- 完成操作数
    [avg_duration] [decimal](8,2) NULL,                 -- 平均耗时
    [min_duration] [int] NULL,                          -- 最小耗时
    [max_duration] [int] NULL,                          -- 最大耗时
    [suspicious_operations] [int] NOT NULL DEFAULT 0,   -- 可疑操作数
    [avg_risk_score] [decimal](5,2) NULL,               -- 平均风险评分
    [created_at] [datetime2](7) NOT NULL DEFAULT GETDATE(),
    [updated_at] [datetime2](7) NOT NULL DEFAULT GETDATE(),
    CONSTRAINT [PK_lzfy_user_behavior_statistics] PRIMARY KEY CLUSTERED ([id] ASC)
)
GO

-- =============================================
-- 创建索引
-- =============================================

-- 用户行为日志表索引
CREATE NONCLUSTERED INDEX [IX_lzfy_user_behavior_log_user_id] ON [dbo].[lzfy_user_behavior_log] ([user_id])
CREATE NONCLUSTERED INDEX [IX_lzfy_user_behavior_log_session_id] ON [dbo].[lzfy_user_behavior_log] ([session_id])
CREATE NONCLUSTERED INDEX [IX_lzfy_user_behavior_log_timestamp] ON [dbo].[lzfy_user_behavior_log] ([timestamp])
CREATE NONCLUSTERED INDEX [IX_lzfy_user_behavior_log_task_detail_id] ON [dbo].[lzfy_user_behavior_log] ([task_detail_id])

-- 用户操作会话表索引
CREATE NONCLUSTERED INDEX [IX_lzfy_user_operation_session_user_id] ON [dbo].[lzfy_user_operation_session] ([user_id])
CREATE NONCLUSTERED INDEX [IX_lzfy_user_operation_session_session_id] ON [dbo].[lzfy_user_operation_session] ([session_id])
CREATE NONCLUSTERED INDEX [IX_lzfy_user_operation_session_start_time] ON [dbo].[lzfy_user_operation_session] ([start_time])
CREATE NONCLUSTERED INDEX [IX_lzfy_user_operation_session_risk_score] ON [dbo].[lzfy_user_operation_session] ([risk_score])

-- 用户行为统计表索引
CREATE NONCLUSTERED INDEX [IX_lzfy_user_behavior_statistics_user_date] ON [dbo].[lzfy_user_behavior_statistics] ([user_id], [stat_date])

-- =============================================
-- 插入初始分析规则
-- =============================================
INSERT INTO [dbo].[lzfy_behavior_analysis_rules] 
([rule_name], [rule_type], [rule_description], [min_duration], [max_duration], [weight])
VALUES 
('正常通话时长', 'DURATION', '正常电话联系的时长范围', 30, 300, 1.0),
('快速操作检测', 'DURATION', '检测过快的操作(可能是虚假操作)', 0, 15, 2.0),
('超长操作检测', 'DURATION', '检测过长的操作(可能忘记保存)', 600, 3600, 1.5),
('输入速度检测', 'INPUT_SPEED', '检测异常的输入速度', NULL, NULL, 1.5),
('批量操作检测', 'BATCH', '检测短时间内的批量操作', NULL, NULL, 2.0);

GO

-- =============================================
-- 添加表注释
-- =============================================
EXEC sys.sp_addextendedproperty 
    @name=N'MS_Description', 
    @value=N'用户行为日志表，记录用户的所有操作行为', 
    @level0type=N'SCHEMA', @level0name=N'dbo', 
    @level1type=N'TABLE', @level1name=N'lzfy_user_behavior_log';

EXEC sys.sp_addextendedproperty 
    @name=N'MS_Description', 
    @value=N'用户操作会话表，记录完整的操作会话信息', 
    @level0type=N'SCHEMA', @level0name=N'dbo', 
    @level1type=N'TABLE', @level1name=N'lzfy_user_operation_session';

EXEC sys.sp_addextendedproperty 
    @name=N'MS_Description', 
    @value=N'用户行为分析规则表，定义行为分析的规则和阈值', 
    @level0type=N'SCHEMA', @level0name=N'dbo', 
    @level1type=N'TABLE', @level1name=N'lzfy_behavior_analysis_rules';

EXEC sys.sp_addextendedproperty 
    @name=N'MS_Description', 
    @value=N'用户行为统计表，按日统计用户行为数据', 
    @level0type=N'SCHEMA', @level0name=N'dbo', 
    @level1type=N'TABLE', @level1name=N'lzfy_user_behavior_statistics';